<?php

declare (strict_types=1);

namespace app\bbxw\controller;


use think\admin\Controller;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
/**
 * 订单管理
 * @class Package
 * @package app\bbxw\controller
 */
class Order extends Controller
{

    /**
     * 绑定数据表
     * @var string
     */
    private $table = 'play_accompany';
    /**
     * 套餐管理
     * @auth true
     * @menu true
     * @login true
     */
    public function index()
    {

        $this->title = '订单管理';
        $query = $this->_query($this->table);
        // 列表排序并显示
        $query->like('customer_name')->equal('status')->order('sort desc')->page();

        $this->fetch();
    }

    /**
     * 图文列表数据处理
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    protected function _page_filter(array &$data)
    {


    }


    /**
     * 添加套餐
     * @auth true
     */
    public function add()
    {
        $this->_form($this->table, 'form');
    }

    /**
     * 表单数据处理
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    protected function _form_filter(array &$data)
    {

    }
    /**
     * 编辑套餐
     * @auth true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function edit()
    {
        $this->_form($this->table, 'form');
    }

    public function backOrder()
    {
        $this->_form($this->table, 'back_order');
    }
    /**
     * 删除套餐
     * auth true
     */
    public function remove()
    {
        $this->_delete($this->table);
    }

    public function updatacustomer(){
        $use_amounts = $this->app->db->name("play_accompany")->field("sum(business_amount) as use_amount,uid")
            ->whereIn("payment_method","扣预存,扣专属预存")->group('uid')->select()->toArray();
        foreach ($use_amounts as $v){
            if($v['use_amount']){
                $this->app->db->name("customer")->where(['id'=>$v['uid']])->update(['use_amount'=>$v['use_amount']]);
            }
        }
        $prestore_amounts = $this->app->db->name("play_accompany")->field("sum(prepaid_amount) as prestore_amount,uid")
           ->group('uid')->select()->toArray();
        foreach ($prestore_amounts as $v){
            if($v['prestore_amount']){
                $this->app->db->name("customer")->where(['id'=>$v['uid']])->update(['prestore_amount'=>$v['prestore_amount']]);
            }
        }

        dump($use_amounts);dump($prestore_amounts);
    }


    public function importorder(){
        $path = $this->request->post('data');
        $path = $this->app->getRootPath()."public/".$path;

        $code = sysqueue("导入订单数据", "xbbxw:importOrder", $later = 0, $data = ['path'=>$path], $rscript = 1, $loops = 0);
        $this->success("任务创建成功",$code);




        // 指定要读取的Excel文件路径
        $spreadsheet = IOFactory::load($path);

        // 获取工作表
        $worksheet = $spreadsheet->getActiveSheet();

        // 初始化一个数组来存储符合条件的记录
        $processedData = [];
        // 遍历工作表中的每一行，从第二行开始
        $rowIterator = $worksheet->getRowIterator();

       // $rowIterator->current()->next(); // 跳过第一行
        // 遍历工作表中的每一行
        foreach ($rowIterator as $row) {
            // 获取当前行的行号
            $rowIndex = $row->getRowIndex();

            // 获取当前行的单元格
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(FALSE); // 这个设置可以让循环包含所有单元格，即使它们没有值

            // 创建一个数组来存储这一行的数据
            $rowData = [];

            foreach ($cellIterator as $k=>$cell) {

                // 获取单元格的值
                $value = $cell->getValue();
                if($k=="A"){
                    // 处理包含`=ROW()-2`公式的单元格
                    if (is_string($value) && strpos($value, '=ROW()-2') !== false) {
                        $value = $rowIndex - 2;
                    }
                }
                if($k=="B"){
                    // 处理日期数据
                    if (is_numeric($value)) {
                        // 尝试将Excel日期序列号转换为标准日期格式
                        try {
                            $value = Date::excelToDateTimeObject($value)->format('Y-m-d');
                        } catch (\Exception $e) {
                            // 如果转换失败，保持原值
                        }
                    }
                }



                // 将处理后的值添加到行数据数组中
                $rowData[] = $value;
            }

            // 将处理后的行数据添加到结果数组中
            $processedData[] = $rowData;
        }
       // dump($processedData);
        $order_data = [];
        foreach ($processedData as $k=>$v){
            if($v[3]&&$k!=0){
                $order_data[]=$v;
            }
        }
        //$data = $this->app->db->name("customer")->where(['name'=>"admin"])->value('id');dump($data);exit;
        $save_data=[];
        foreach ($order_data as $v){
            $name = $v[3];
            $uid = $this->app->db->name("customer")->where(['name'=>$name])->value('id');
           // dump($uid);
            if(empty($uid)){
                $uid = $this->app->db->name("customer")->insertGetId(['name'=>$name,'nickname'=>$v[4],'password'=>md5("123123")]);
            }
            //dump(111);
            $save_data[] = [
                'sort'=>$v[0],
                'date'=>$v[1],
                'group_number'=>$v[2],
                'customer_name'=>$v[3],
                'nickname'=>$v[4],
                'activity_gift'=>$v[5],
                'play_duration_h'=>$v[6],
                'order_quantity'=>$v[7],
                'power_source'=>$v[8],
                'receptionist'=>$v[9],
                'collector'=>$v[10],
                'business_amount'=>$v[11],
                'payment_method'=>$v[12],
                'non_business_income'=>$v[13],
                'non_business_expense'=>$v[14],
                'refund_dove_fee'=>$v[15],
                'prepaid_amount'=>$v[16],
                'remarks'=>$v[17],
                'uid'=>$uid
            ];

        }

        $this->app->db->name($this->table)->insertAll($save_data);
        $this->success("导入成功");
        dump($order_data);

    }

}